BigQuery で GCS 外部テーブルにもキャッシュが使えるようになったので、どのくらい速くなるのか確認してみた

#Google BigQuery

#Google Cloud (GCP)

みかみ

2021.02.18

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

2021/02/12 の BigQuery のリリースで、GCS ファイルをソースデータとする外部テーブルに対する SQL クエリでキャッシュが利用できるようになったそうです！

やりたいこと

GCS 外部テーブルへの SQL で本当にキャッシュが使えるようになったのか確認したい
キャッシュを利用した場合の使用スロット数を確認したい
キャッシュ有無で SQL 実行時間と使用スロット数がどのくらい変わるか確認したい
実テーブルと外部テーブルで SQL 実行時間がどのくらい変わるか確認したい

外部テーブルを準備

Kaggle のデータセットから、以下のアメリカのアボカドの価格と販売数のデータをいただいてきました。

index,Date,AveragePrice,Total Volume,4046,4225,4770,Total Bags,Small Bags,Large Bags,XLarge Bags,type,year,region
0,2015-12-27,1.33,64236.62,1036.74,54454.85,48.16,8696.87,8603.62,93.25,0.0,conventional,2015,Albany
1,2015-12-20,1.35,54876.98,674.28,44638.81,58.33,9505.56,9408.07,97.49,0.0,conventional,2015,Albany
2,2015-12-13,0.93,118220.22,794.7,109149.67,130.5,8145.35,8042.21,103.14,0.0,conventional,2015,Albany
3,2015-12-06,1.08,78992.15,1132.0,71976.41,72.58,5811.16,5677.4,133.76,0.0,conventional,2015,Albany
（省略）
10,2018-01-14,1.93,16205.22,1527.63,2981.04,727.01,10969.54,10919.54,50.0,0.0,organic,2018,WestTexNewMexico
11,2018-01-07,1.62,17489.58,2894.77,2356.13,224.53,12014.15,11988.14,26.01,0.0,organic,2018,WestTexNewMexico

Avocado Prices ｜ Kaggle

ダウンロードした CSV ファイルを GCS にアップロードし、下記 Python コードで GCS のファイルデータを参照する外部テーブル、avocado_ext テーブルを作成しました。

from google.cloud import bigquery

table_id = "cm-da-mikami-yuki-258308.dataset_1.avocado_ext"

client = bigquery.Client()
table = bigquery.Table(table_id)
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://test-mikami/avocado.csv"
]
external_config.options.skip_leading_rows = 1
external_config.autodetect = True
table.external_data_configuration = external_config
table = client.create_table(table)

外部テーブルへの SQL でキャッシュが利用されるか確認

BigQuery のクエリエディタから、各年ごとの平均販売価格と販売個数を取得する以下の SQL を実行してみます。

SELECT
  year,
  region,
  AVG(AveragePrice) AS avg_price,
  AVG(Total_Volume) AS avg_count
FROM
  dataset_1.avocado_ext
GROUP BY
  year, region
ORDER BY
  year
;

1.9 MB が処理されて、0.6 秒で結果が取得できました。

同じ SQL をもう一度実行してみます。

今度は処理データ量の表示が「キャッシュ済み」になり、GCS ファイルをソースデータとする外部テーブルでもキャッシュが効いていることが確認できました！

キャッシュ有効な場合の使用スロット数を確認

BigQuery のドキュメントに、以下の記載がありました。

クエリ結果がキャッシュ結果テーブルから取得された場合、ジョブ統計プロパティ statistics.query.cacheHit は true として返され、そのクエリについては課金されません。

抜粋：料金と割り当て｜ BigQuery ドキュメント

BigQuery のクエリ料金は処理データ量（読み取りバイト数）に従って課金されるので、処理データ量が 0 であれば料金はかからないわけですね。

オンデマンド料金｜ BigQuery ドキュメント

BigQuery の料金プランは、処理データ量に従って課金されるオンデマンド料金と定額料金の２種類から選択できます。定額料金では、BigQuery の処理能力の単位であるスロット数に従って料金が決定します。

キャッシュを利用した場合に処理データ量が 0 になることは確認できましたが、使用するスロット数はどうなるのでしょうか？

先ほどは BigQuery 管理コンソールから確認しましたが、SQL 実行時のキャッシュ利用有無、処理時間や処理データ量、利用したスロット数などは、INFORMATION_SCHEMA の JOBS_BY_PROJECT ビューからも確認することができます。

INFORMATION_SCHEMA を使用したジョブメタデータの取得｜ BigQuery ドキュメント

以下の SQL で、先ほど実行した SQL のキャッシュ利用などの情報を取得してみます。

SELECT
  start_time,
  end_time,
  cache_hit,
  total_bytes_processed,
  total_slot_ms,
  query,
  state
FROM `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time BETWEEN
  	TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
    AND CURRENT_TIMESTAMP()
ORDER BY creation_time DESC
LIMIT 5;

キャッシュを利用した場合、使用スロット数も 0 で結果が取得できることが確認できました！

キャッシュを使うとどのくらい速くなるのか確認

では、キャッシュが有効な場合、SQL の実行時間はどのくらい速くなるのでしょうか？

前のセクションでは SQL のキャッシュ利用等の情報を INFORMATION_SCHEMA から確認しましたが、BigQuery API を利用する場合にはクエリ結果から確認することもできます。 Python クライアントライブラリ経由の操作も BigQuery API をコールすることになるので、クエリ結果でキャッシュ利用などの情報を確認できます。

同じ SQL をキャッシュ有りと無しで 10 回ずつ実行した場合の平均処理時間と利用スロット数を確認するため、以下の Python コードを準備しました。

from google.cloud import bigquery
import argparse
import numpy

parser = argparse.ArgumentParser(description='project')
parser.add_argument('cache', help='use query cache')
parser.add_argument('count', help='execute query count')
args = parser.parse_args()

job_config = bigquery.QueryJobConfig(use_query_cache=args.cache)
query = (
    'SELECT year, region, AVG(AveragePrice) AS avg_price, AVG(Total_Volume) AS avg_count '
    'FROM dataset_1.avocado_ext GROUP BY year, region ORDER BY year'
)
client = bigquery.Client()

list_delta = []
list_slot = []
for i in range(int(args.count)):
    query_job = client.query(query, job_config=job_config)
    result = query_job.result()
    print('cache_hit: {}'.format(query_job.cache_hit))
    time_delta = (query_job.ended-query_job.started).total_seconds()
    list_delta.append(time_delta)
    print('exec time: {}'.format(time_delta))
    slots = query_job.slot_millis or 0
    list_slot.append(slots)
    print('slot_millis: {}'.format(slots))
    print('========')

print('average time: {}'.format(round(numpy.average(list_delta), 3)))
print('average slot: {}'.format(round(numpy.average(list_slot), 3)))

キャッシュを利用するかどうかと SQL を何回実行するかは、実行引数で指定します。まずはキャッシュ無効で 10 回実行してみます。

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ python3 check_cache.py False 10
cache_hit: False
exec time: 0.571
slot_millis: 121
========
cache_hit: False
exec time: 0.572
slot_millis: 132
========
cache_hit: False
exec time: 0.528
slot_millis: 123
========
cache_hit: False
exec time: 0.625
slot_millis: 186
========
cache_hit: False
exec time: 0.479
slot_millis: 122
========
cache_hit: False
exec time: 0.501
slot_millis: 142
========
cache_hit: False
exec time: 0.563
slot_millis: 132
========
cache_hit: False
exec time: 0.395
slot_millis: 88
========
cache_hit: False
exec time: 0.387
slot_millis: 88
========
cache_hit: False
exec time: 0.543
slot_millis: 129
========
average time: 0.516
average slot: 126.3

キャッシュ無効の場合の平均処理時間は約 0.516 秒（516ms）で、使用スロット数は約 126 スロットでした。

続いて同様にキャッシュを有効にした場合も確認してみます。

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ python3 check_cache.py True 10
cache_hit: True
exec time: 0.011
slot_millis: 0
========
cache_hit: True
exec time: 0.011
slot_millis: 0
========
cache_hit: True
exec time: 0.012
slot_millis: 0
========
cache_hit: True
exec time: 0.009
slot_millis: 0
========
cache_hit: True
exec time: 0.011
slot_millis: 0
========
cache_hit: True
exec time: 0.011
slot_millis: 0
========
cache_hit: True
exec time: 0.011
slot_millis: 0
========
cache_hit: True
exec time: 0.01
slot_millis: 0
========
cache_hit: True
exec time: 0.011
slot_millis: 0
========
cache_hit: True
exec time: 0.01
slot_millis: 0
========
average time: 0.011
average slot: 0.0

キャッシュ有効で SQL を 10 回実行した場合の平均処理時間は約 0.11 秒（11ms）で、使用スロット数は 0 です。キャッシュ無効な場合の SQL 処理時間、0.516 秒（516ms）と比べると、はるかに速くなっていることが確認できました！

実テーブルと外部テーブルでは処理時間がどのくらい変わるのか確認

GCS ファイルをソースデータとする外部テーブルでは、データを BigQuery に取り込む手間がかからない反面、SQL のパフォーマンスは悪くなってしまう印象です。キャッシュを利用できるのであれば、外部テーブルでもパフォーマンスはそれほど悪くならないのでは？！

データを BigQuery で持つ実テーブルと、GCS ファイルを参照する外部テーブルでは、SQL の実行時間にどのくらい差が出るのか確認してみます。

外部テーブルで参照している CSV ファイルデータを BigQuery にロードして、avocado という名前の実テーブルを作成しました。

前のセクションで検証に使用した Python コードの SQL の FROM 句を実テーブルに変更して、キャッシュ有効で実行してみます。

（省略）
query = (
    'SELECT year, region, AVG(AveragePrice) AS avg_price, AVG(Total_Volume) AS avg_count '
    'FROM dataset_1.avocado GROUP BY year, region ORDER BY year'
)
（省略）

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ python3 check_cache_norm.py True 10
cache_hit: True
exec time: 0.011
slot_millis: 0
========
（省略）
========
average time: 0.011
average slot: 0.0

平均処理時間は約 0.11 秒（11ms）で、外部テーブルに対してキャッシュ有効で SQL を実行した場合と変わりません！

なお、実テーブルに対してキャッシュ無効で SQL を実行した場合の平均処理時間は、約 0.274 秒（274ms）でした。

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ python3 check_cache_norm.py False 10
cache_hit: False
exec time: 0.314
slot_millis: 95
========
（省略）
========
average time: 0.274
average slot: 86.9

外部テーブルに対してキャッシュ無効で SQL 実行した場合の処理時間は約 0.516 秒（516ms）だったので、キャッシュが使えないとやはり外部テーブルへの SQL パフォーマンスはあまりよくありませんが、キャッシュが利用できれば外部テーブルのパフォーマンスは気にする必要がなくなったようです！

まとめ（所感）

検証で使用したテーブルのレコード数は 18,249 行、データ量は 2.08 MB でした。また、実行した SQL もグルーピングと集計関数を使っただけの、それほど複雑ではない SQL です。テーブルのデータ量や実行する SQL の複雑さによっては、キャッシュ利用によるパフォーマンスの改善はより期待できるはずです！

キャッシュデータの保持期間は約 24h とのことなので全てのユースケースでパフォーマンス改善が見込めるわけではありませんが、 GCS ファイルをソースデータとする外部テーブルでもキャッシュが利用できるようになった今回のリリースによって、BigQuery はさらに使いやすくなったのではないでしょうか？！